(ns com.m3958.firstgwt.server.servlet.ClojureServlet
	(:require [clojure.java.jdbc :as sql])
  (:require clojure.string)
  (:require clojure.data.json) 
	(:import com.mchange.v2.c3p0.ComboPooledDataSource)
  (:import java.util.HashMap)
  (:import java.util.ArrayList))

(defn init-db-connection [u p]
	(def db-spec
	  {:classname "com.mysql.jdbc.Driver"
	   :subprotocol "mysql"
	   :subname "//127.0.0.1:3306/FREERECORD?characterEncoding=utf8"
	   :user u
	   :password p}) 

	(defn pool
	  [spec]
	  (let [cpds (doto (ComboPooledDataSource.)
		       (.setDriverClass (:classname spec)) 
		       (.setJdbcUrl (str "jdbc:" (:subprotocol spec) ":" (:subname spec)))
		       (.setUser (:user spec))
		       (.setPassword (:password spec))
		       ;; expire excess connections after 30 minutes of inactivity:
		       (.setMaxIdleTimeExcessConnections (* 30 60))
		       ;; expire connections after 3 hours of inactivity:
		       (.setMaxIdleTime (* 3 60 60)))]
	    {:datasource cpds}))
	
	(def pooled-db (delay (pool db-spec)))
	(defn db-connection [] @pooled-db);@Deref
)

;20strings,10integer,10double,10boolean,5date,5datetime,5text,2longtext
(defn create-table-vector [pfx,num,dd]
	(reduce 
		(fn [l i] (cons [(keyword (str pfx i)) dd "DEFAULT NULL"] l))
		[]
		(range num)
	)
)
(defn create-free-record-table
  "Create a table"
  []
  (sql/with-connection (db-connection)
     (try
	  (apply sql/create-table (concat [:FREETABLE [:freerecord_id "int" "NOT NULL" "PRIMARY KEY" "AUTO_INCREMENT"]
                                    [:audit "int(1)" "DEFAULT 0"]
                                    [:public "int(1)" "DEFAULT 0"]
                                    [:_tableschemaid "int(11)" "NOT NULL"]
                                    [:createdat "TIMESTAMP" "DEFAULT CURRENT_TIMESTAMP"]]
	  		  (create-table-vector "s" 20 "varchar(255)")
	  		  (create-table-vector "i" 10 "int(11)")
	  		  (create-table-vector "do" 10 "double")
	  		  (create-table-vector "b" 10 "int(1)")
	  		  (create-table-vector "da" 5 "date")
	  		  (create-table-vector "dt" 5 "datetime")
	  		  (create-table-vector "t" 5 "text")
	  		  (create-table-vector "lt" 2 "longtext")
	  		  )
	  	  )
      (catch Exception e (println e)(println "table exist!"))
      )
	  )
)

(defn set-redis-pool [rp]
  (def redis-pool rp)
  )

(defn start [user password]
  (init-db-connection user password)
  (create-free-record-table)
)

;20strings,10integer,10double,10boolean,5date,5datetime,5text,2longtext
(def table-fields ;所有可能的表字段的集合，用来过滤httprequest中的非数据存储字段。
  (set
	(concat
    #{"audit" "public","_tableschemaid"}
		(reduce (fn [ss num] (conj ss (str "s" num))) #{} (range 20))
		(reduce (fn [is num] (conj is (str "i" num))) #{} (range 10))
		(reduce (fn [dos num] (conj dos (str "do" num))) #{} (range 10))
		(reduce (fn [bs num] (conj bs (str "b" num))) #{} (range 10))
		(reduce (fn [das num] (conj das (str "da" num))) #{} (range 10))
		(reduce (fn [dts num] (conj dts (str "dt" num))) #{} (range 5))
		(reduce (fn [ts num] (conj ts (str "t" num))) #{} (range 5))
		(reduce (fn [lts num] (conj lts (str "lt" num))) #{} (range 2))
	)))

(defn create-record-map-from-req
  "生成记录hashmap"
   [http-req]
	(reduce (fn [hm pair]
           (let [[k v] [(first pair) (first (nth pair 1))]]
             (if (or (= "audit" k) (= "public" k)) (if v (assoc hm k 1)) (assoc hm k v))
           )) {};重新组合成hashmap,
         (filter (fn [item] (table-fields (key item))) (into {} (.getParameterMap http-req)));从map里面找出属于数据库字段的内容。结果集不再是hashmap，而是seq。(["pn1",["v1" "v2"]] ["pn2",["v3" "v4"]])
	))

(defn create-fetch-map-from-req
  ""
  [http-req]
	(reduce (fn [hm pair]
           (let [[k v] [(first pair) (first (nth pair 1))]]
             (assoc hm k v)
           )) {};重新组合成hashmap,
         (into {} (.getParameterMap http-req));
	))


(defn convert-record-to-map
  "将一条记录从keyword map转换成string map"
  [record]
  (java.util.HashMap.
    (reduce (fn [m item] (assoc m (name (key item)) (val item))) {} record)))

(defn convert-records-to-array
  "将一条记录从keyword map转换成string map"
  [records]
  (java.util.ArrayList.
    (map convert-record-to-map records)))

(defn read-one-record
  "Read the entire fruit table"
  [fieldset id]
  (sql/with-connection (db-connection)
    (sql/with-query-results res
      [(str "SELECT "
            (clojure.string/join "," fieldset)
            ", freerecord_id "
            " FROM FREETABLE WHERE freerecord_id = ?") id]
      (clojure.data.json/json-str {"response" {"status" 0 "startRow" 0 "endRow" 1 "totalRows" 1 "data" res}}))))

(defn insert-one-record 
  "插入"
  [http-req fieldset]
  (read-one-record fieldset
  (val
  (first
  (sql/with-connection (db-connection)
  (sql/insert-record
    :FREETABLE
    (create-record-map-from-req http-req)))))))

(defn get-limit-offset
  ""
  [fetch-paras]
  (println fetch-paras)
  (let [[start-row end-row] [(read-string (fetch-paras "_startRow")) (read-string (fetch-paras "_endRow"))]]
    (println {:limit (- end-row start-row) :offset start-row})
  {:limit (- end-row start-row) :offset start-row :start-row start-row :end-row end-row}
  ))

(defn read-record-range
  "Print rows describing fruit that are within a grade range"
  [http-req fieldset]
  (let [fetch-paras (create-fetch-map-from-req http-req) limit-offset (get-limit-offset fetch-paras)]
  (sql/with-connection (db-connection)
    (sql/with-query-results res
      [(str "SELECT "
            (clojure.string/join ", " fieldset)
            " , freerecord_id "
            " FROM FREETABLE WHERE _tableschemaid = ?"
            "LIMIT ? OFFSET ?")
        (fetch-paras "_tableschemaid") (limit-offset :limit) (limit-offset :offset)
        ]
       (clojure.data.json/json-str {"response" {"status" 0 "startRow" (limit-offset :start-row) "endRow" (+ (limit-offset :start-row) (count res)) "totalRows" (count res) "data" res}})
       ))))
